USE [NANS21V]
GO

/****** Object:  StoredProcedure [dbo].[ResultTrackLapKiroku]    Script Date: 04/25/2014 19:56:47 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ResultTrackLapKiroku]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ResultTrackLapKiroku]
GO

--2014/05/13
--リザルト記録のラップの記録を出力
--SQL_SEL0606
--LAPTBL  base

CREATE PROCEDURE [dbo].[ResultTrackLapKiroku]
	@TAIKAINO int,
	@KYOGINO int,
	@ROUNDNO int,
	@SUBNO int,
	@KUMINO int,
	@SYUMOKUKUBUN int --add (混成ならサブ種目を渡すこと)
AS


BEGIN
SELECT 

L.LAPNO,
L.ID, 
LM.LAPMEI,
dbo.Kiroku(RL.KIROKU,@SYUMOKUKUBUN,SYUMO.KIROKUKUBUN,RL.KYOGIFLG2) AS KIROKU,
RL.NUMBER,
RL.KYOGIFLG2

 FROM TBL_LAP AS L 
LEFT OUTER JOIN (SELECT RL.LAPNO, RL.KIROKU,
 RL.NUMBER, RL.KYOGIFLG2
 
 FROM TBL_RESULT_LAP 
AS RL WHERE RL.TAIKAINO = @TAIKAINO 
AND RL.KYOGINO = @KYOGINO 
AND RL.SUBNO = @SUBNO 
AND RL.ROUNDNO = @ROUNDNO 
AND RL.KUMINO = @KUMINO ) AS RL ON L.LAPNO = RL.LAPNO,
 TBL_KYOGI AS K, TBL_LAP_MASTER AS LM 
 , TBL_SYUMOKU_MASTER AS SYUMO --add
WHERE L.LAPNO = LM.LAPNO 
AND K.TAIKAINO = @TAIKAINO 
AND K.KYOGINO = @KYOGINO 
AND K.SYUMOKUNO = L.SYUMOKUNO 
AND K.SYUMOKUNO =SYUMO.SYUMOKUNO--add
AND SYUMO.SYUMOKUKUBUN =@SYUMOKUKUBUN

UNION --結果結合

SELECT 
L.LAPNO,
L.ID,
 LM.LAPMEI,
 (CASE WHEN RL.KIROKU = 0 THEN '' ELSE 
dbo.Kiroku(RL.KIROKU,@SYUMOKUKUBUN,SYUMO.KIROKUKUBUN,RL.KYOGIFLG2)
END )AS KIROKU,
RL.NUMBER,
RL.KYOGIFLG2 

FROM TBL_LAP AS L 
LEFT OUTER JOIN (SELECT RL.LAPNO, RL.KIROKU,
 RL.NUMBER, RL.KYOGIFLG2 
 FROM TBL_RESULT_LAP AS RL 
WHERE RL.TAIKAINO = @TAIKAINO 
AND RL.KYOGINO = @KYOGINO 
AND RL.SUBNO = @SUBNO 
AND RL.ROUNDNO = @ROUNDNO 
AND RL.KUMINO = @KUMINO ) AS RL ON L.LAPNO = RL.LAPNO
, TBL_KYOGI AS K, TBL_LAP_MASTER AS LM 
, TBL_SYUMOKU_MASTER AS SYUMO --add
WHERE L.LAPNO = LM.LAPNO AND L.SYUMOKUNO = @SUBNO 
AND K.SYUMOKUNO =SYUMO.SYUMOKUNO--add
AND SYUMO.SYUMOKUKUBUN =@SYUMOKUKUBUN
ORDER BY ID

END